package com.appkubes.fintech.admin.application.bill.dao;

import cn.hutool.core.date.DateUtil;
import com.appkubes.fintech.admin.application.bill.entity.dto.BillReportVO;
import com.appkubes.fintech.admin.application.bill.entity.vo.RentBillHeaderRequest;
import com.appkubes.fintech.admin.application.user.entity.vo.UserBaseInfoVO;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.jdbc.SQL;

import java.util.Objects;

public class RentBillHeaderSqlProvider {
    public String select(RentBillHeaderRequest request) {
        return new SQL() {
            {
                SELECT(" hea.periods, ore.goods_title as goodsTitle, hea.bill_no as billNo,ore.rent_type as rentType, hea.bill_interest as billInterest,hea.repay_interest repayInterest,hea.surp_interest_amount as surpInterestAmount,hea.bill_fine as billFine, " +
                        " hea.unpay_amount as unpayAmount ,hea.unpay_interest_amount as unpayInterestAmount , hea.interest_rate as interestRate, hea.bill_key as billKey ,hea.rent_end_date as rentEndDate, ba.real_name as realName,ba.phone as phone ,hea.bill_amount as billAmount ,hea.repay_amount as repayAmount ,hea.surp_amount as surpAmount,hea.rent_start_date as rentStartDate ,hea.rent_end_date as rentEndDate,hea.status" +
                        " ,hea.unpay_periods as unpayPeriods,hea.user_key as userKey " +
                        " , hea.update_time as updateTime,hea.repay_fine_amount as repayFineAmout " +
                        " ,hea.surp_fine_amount as surpFineAmount ,ore.buy_out_amount as buyOutAmount" +
                        " ,hea.unpay_date as unpayDate,hea.unpay_periods as unpayPeriods,ore.rent_amount as rentAmount,ba.ref_user_name as refUserName,ba.ref_user_phone as refUserPhone,ba.ref_channel_name as refChannelName,ba.age as age  ");
                FROM(" fintech_rent_bill_header hea,fintech_rent_order ore,fintech_user_base_info ba  ");
                StringBuilder whereClause = new StringBuilder(" hea.order_key=ore.rent_order_key and ba.user_key=hea.user_key ");

                if (StringUtils.isNotBlank(request.getBillNo())) {
                    whereClause.append(" and hea.bill_no = '").append(request.getBillNo()).append("' ");
                }
                if (StringUtils.isNotBlank(request.getRealName())) {
                    whereClause.append(" and ba.real_name like '%").append(request.getRealName()).append("%' ");
                }
                if (StringUtils.isNotBlank(request.getPhone())) {
                    whereClause.append(" and ba.phone = '").append(request.getPhone()).append("' ");
                }
                if (StringUtils.isNotBlank(request.getStatus())) {
                    whereClause.append(" and hea.status = '").append(request.getStatus()).append("' ");
                }
                if (request.getStartDate() != null) {
                    whereClause.append(" and hea.create_time >= '").append(DateUtil.format(request.getStartDate(), "yyyy-MM-dd HH:mm:ss")).append("' ");
                }
                if (request.getEndDate() != null) {
                    whereClause.append(" and hea.create_time <= '").append(DateUtil.format(request.getEndDate(), "yyyy-MM-dd HH:mm:ss")).append("' ");
                }

                if (request.getRePayStartDate() != null) {
                    whereClause.append(" and hea.unpay_date >= '").append(DateUtil.format(request.getRePayStartDate(), "yyyy-MM-dd")).append("' ");
                }
                if (request.getRePayEndDate() != null) {
                    whereClause.append(" and hea.unpay_date <= '").append(DateUtil.format(request.getRePayEndDate(), "yyyy-MM-dd")).append("' ");
                }

                if (StringUtils.isNotBlank(request.getRefChannelName())) {
                    whereClause.append(" and ba.ref_channel_name = '").append(request.getRefChannelName()).append("' ");
                }
                if (StringUtils.isNotBlank(request.getRefUserPhone())) {
                    whereClause.append(" and ba.ref_user_phone = '").append(request.getRefUserPhone()).append("' ");
                }


                if (!"".equals(whereClause.toString())) {
                    WHERE(whereClause.toString());
                }
                ORDER_BY("unpay_date asc,hea.update_time asc   ");
                //  ORDER_BY( "hea.update_time desc   ");
            }
        }.toString();
    }

    /**
     * @Author: XQC 2020/1/4/17:16
     * @params: 认证用户查询
     * @return:
     * @Description:
     **/
    public String selectAuth(UserBaseInfoVO request) {
        return new SQL() {
            {
                SELECT(" base.user_key as userKey, base.real_name AS realName,base.phone ,base.identity_num as idcardNumber,cer.is_base_info_certificate phoneStatus " +
                        ",cer.is_all_certificate AS portraitStatus ,base.living_city as livingCity,base.living_province as livingProvince,base.ref_user_name as refUserName,base.ref_channel_name refChannelName" +
                        ",cer.is_real_name_certificate AS idcardStatus,base.create_time AS createTime,base.sex,dit.credit_status as creditStatus,dit.credit_amount as creditTotalAmt ,dit.freeze_amount as creditFreeAmt,dit.forzen_amount as forzenAmount " +
                        ",base.user_source as userSource,cer.certificate_3 as certificate3,cer.is_operator_get as is_operator_get," +
                        " base.user_device as userDevice,base.age,base.update_time as updateTime ,base.is_new as isNew,base.ref_user_phone as refUserPhone ,cer.is_all_certificate as isAllAuth,dit.approval as remark");
                FROM(" fintech_user_base_info base LEFT JOIN fintech_user cer ON cer.user_key=base.user_key LEFT JOIN fintech_credit dit ON cer.user_key=dit.user_key  ");
                StringBuilder whereClause = new StringBuilder("  1 ");

                if (StringUtils.isNotBlank(request.getRealName())) {
                    whereClause.append(" and base.real_name like '%").append(request.getRealName()).append("%' ");
                }
                if (StringUtils.isNotBlank(request.getRefUserName())) {
                    whereClause.append(" and base.ref_user_name like '%").append(request.getRefUserName()).append("%' ");
                }
                if (StringUtils.isNotBlank(request.getRefChannelName())) {
                    whereClause.append(" and base.ref_channel_name like '%").append(request.getRefChannelName()).append("%' ");
                }
                if (StringUtils.isNotBlank(request.getRefGroupName())) {
                    whereClause.append(" and base.ref_group_name like '%").append(request.getRefGroupName()).append("%' ");
                }
                if (StringUtils.isNotBlank(request.getPhone())) {
                    whereClause.append(" and base.phone = '").append(request.getPhone()).append("' ");
                }
                if(Objects.equals("1",request.getIsNew())){
                    whereClause.append(" and  ( base.is_new is null or  base.is_new='").append(request.getIsNew()).append("') ");
                }
                if(Objects.equals("0",request.getIsNew())){
                    whereClause.append(" and    base.is_new='").append(request.getIsNew()).append("' ");
                }


                if (StringUtils.isNotBlank(request.getIdentityNum())) {
                    whereClause.append(" and base.identity_num = '").append(request.getIdentityNum()).append("' ");
                }

                if (StringUtils.isNotBlank(request.getRefUserPhone())) {
                    whereClause.append(" and base.ref_user_phone = '").append(request.getRefUserPhone()).append("' ");
                }
                if (request.getMemberStarus() != null) {
                    whereClause.append(" and cer.is_all_certificate = '").append(request.getMemberStarus()).append("' ");
                }

                if (StringUtils.isNotBlank(request.getCustomerSource())) {
                    whereClause.append(" and base.user_source = '").append(request.getCustomerSource()).append("' ");
                }

                if (StringUtils.isNotBlank(request.getAuthType())) { //认证状态
                    whereClause.append(" and dit.credit_status = '").append(request.getAuthType()).append("' ");

                }
                if (request.getStartDate() != null) {
                    whereClause.append(" and base.create_time >= '").append(DateUtil.format(request.getStartDate(), "yyyy-MM-dd HH:mm:ss")).append("' ");
                }
                if (request.getEndDate() != null) {
                    whereClause.append(" and base.create_time <= '").append(DateUtil.format(request.getEndDate(), "yyyy-MM-dd HH:mm:ss")).append("' ");
                }
                if (request.getPhoneStatus() != null) {
                    whereClause.append(" and cer.is_base_info_certificate = '").append(request.getPhoneStatus()).append("' ");
                }
                if (request.getIdCardStatus() != null) {
                    whereClause.append(" and cer.is_real_name_certificate = '").append(request.getIdCardStatus()).append("' ");
                }
                if (request.getPortraitStatus() != null) {
                    whereClause.append(" and cer.is_bank_card_certificate = '").append(request.getPortraitStatus()).append("' ");
                }
                if (!"".equals(whereClause.toString())) {
                    WHERE(whereClause.toString());
                }
                ORDER_BY("base.create_time desc ");
            }
        }.toString();
    }

    /**
     * @Author: XQC 2020/1/4/17:16
     * @params: 认证用户查询
     * @return:
     * @Description:
     **/
    public String billReport(BillReportVO vo) {
        return new SQL() {
            {
                SELECT(" SUM(lin.period_amount) as payAmt,SUM(lin.period_interest_amount) as interestAmt,SUM(lin.period_fine_amount) as fineAmt, lin.period_end_date AS repayDate, SUM(lin.unpay_fine_amount) as unpayFineAmt,SUM(lin.unpay_amount) unpayAmt ,SUM(lin.unpay_interest_amount) AS unpayIntegerestAmt,SUM(lin.repay_amount) AS repayAmt \n" +
                        ",SUM(lin.repay_interest_amount) AS repayInterestAmt ,SUM(lin.repay_fine_amount) as repayFineAmt ");
                FROM(" fintech_rent_bill_lines lin  ");
                StringBuilder whereClause = new StringBuilder("  1=1 ");

                if (vo.getStartDate() != null) {
                    whereClause.append(" and lin.period_end_date >= '").append(DateUtil.format(vo.getStartDate(), "yyyy-MM-dd")).append("' ");
                }
                if (vo.getEndDate() != null) {
                    whereClause.append(" and lin.period_end_date <= '").append(DateUtil.format(vo.getEndDate(), "yyyy-MM-dd")).append("' ");
                }
                if (!"".equals(whereClause.toString())) {
                    WHERE(whereClause.toString());
                }
                GROUP_BY(" lin.period_end_date");
                ORDER_BY("lin.period_end_date");
            }
        }.toString();
    }

}
